CREATE PROCEDURE Category_FindAll
AS
BEGIN
    Select 
    CategoryId,
    ApplicationId,
    Name,
    Description,
    Picture,
    ParentId,
    IsActive,
    SortIndex
    from Category
END
GO

CREATE PROCEDURE Category_FindById
@CategoryId int
AS
BEGIN
    Select * from Category where CategoryId = @CategoryId
END
GO

CREATE PROCEDURE Category_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Category where CategoryId = @name
END
GO

CREATE PROCEDURE Category_Insert
        @CategoryId int ,
        @ApplicationId int ,
        @Name nvarchar (50),
        @Description nvarchar (250),
        @Picture nvarchar (250),
        @ParentId int ,
        @IsActive bit ,
        @SortIndex int 
AS
BEGIN
INSERT INTO Category
(
        CategoryId,
        ApplicationId,
        Name,
        Description,
        Picture,
        ParentId,
        IsActive,
        SortIndex
)
VALUES
(
        @CategoryId,
        @ApplicationId,
        @Name,
        @Description,
        @Picture,
        @ParentId,
        @IsActive,
        @SortIndex
)
select @CategoryId = @@IDENTITY
END
GO

CREATE PROCEDURE Category_Update
        @CategoryId int ,
        @ApplicationId int ,
        @Name nvarchar (50),  
        @Description nvarchar (250),  
        @Picture nvarchar (250),  
        @ParentId int ,
        @IsActive bit ,
        @SortIndex int
AS
BEGIN
UPDATE  Category SET
    ApplicationId = @ApplicationId,
    Name = @Name,
    Description = @Description,
    Picture = @Picture,
    ParentId = @ParentId,
    IsActive = @IsActive,
    SortIndex = @SortIndex
    WHERE CategoryId = @CategoryId
END
GO

CREATE PROCEDURE Category_Delete
    @CategoryId int
AS
BEGIN
    DELETE FROM Category where CategoryId = @CategoryId
END
GO

CREATE PROCEDURE Category_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(CategoryId) FROM Category;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Category
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Category_SetActive
@CategoryId int
AS
declare @Type bit
SELECT @Type =  IsActive from Category where CategoryId = @CategoryId
BEGIN
IF(@Type = 1)
Update Category set IsActive = 0 where CategoryId = @CategoryId
else
Update Category set IsActive = 1 where CategoryId = @CategoryId
END
GO

CREATE proc Category_SetSortIndex
@CategoryId int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Category where CategoryId = @CategoryId
BEGIN
IF(@Type = 1)
Update Category set SortIndex =  @CurrentSort + 1 where CategoryId = @CategoryId
else
Update Category set SortIndex =  @CurrentSort - 1 where CategoryId = @CategoryId
END
GO

CREATE PROCEDURE Category_RemoveSelectedItem
    @CategoryId int
AS
BEGIN
    UPDATE Category SET IsDeleted = 1 WHERE CategoryId = @CategoryId
END
GO

CREATE PROCEDURE Category_RestoreSelectedItem
    @CategoryId int
AS
BEGIN
    UPDATE Category SET IsDeleted = 0 WHERE CategoryId = @CategoryId
END
GO

CREATE PROCEDURE Category_RemoveAll
    @CategoryId int
AS
BEGIN
    UPDATE Category SET IsDeleted = 1
END
GO

CREATE PROCEDURE Category_RestoreAll
    @CategoryId int
AS
BEGIN
    UPDATE Category SET IsDeleted = 0
END
GO

